//
//  DataBaseHandler.m
//  IntimateGift
//
//  Created by zh on 14/12/25.
//  Copyright (c) 2014年 蔡宏业. All rights reserved.
//

#import "DataBaseHandler.h"

@implementation DataBaseHandler
{
    UIAlertView *alertView;

}

// 1.类方法
+ (DataBaseHandler *)shareInstance
{
    // 当第一次执行的时候会产生一个空指针
    static DataBaseHandler *handler = nil;
    // 对指针进行判断，当第一次执行的时候创建一个对象
    if (handler == nil) {
        handler = [[DataBaseHandler alloc] init];
    }
    // 无论创建的和已经存在的，都在这里直接return回去
    return handler;
}

- (BOOL)judgeResult:(int)result text:(NSString *)text
{
    if (result == SQLITE_OK) {
        NSLog(@"%@成功", text);
        return YES;
    } else {
        NSLog(@"%d", result);
        NSLog(@"%@失败", text);
        return NO;
    }
}

// 打开数据库
- (BOOL)openDB
{
    // 打开数据库
    
    // 1.拼接一个数据库文件路径
    NSString *docPath = [NSSearchPathForDirectoriesInDomains(NSDocumentDirectory, NSUserDomainMask, YES) lastObject];
    // 在documents文件夹下拼接一个数据库文件地址
    NSString *dbPath = [docPath stringByAppendingPathComponent:@"IntimateGift.db"];
    int result = sqlite3_open([dbPath UTF8String], &dbPoint);
    return [self judgeResult:result text:@"打开"];
}

// 关闭数据库
- (BOOL)closeDB
{
    // 关闭数据库
    int result = sqlite3_close(dbPoint);
    // 利用封装的方法判断结果
    return [self judgeResult:result text:@"关闭"];
}

// 创建精选表
- (BOOL)createChoicenessTable
{
    NSString *sql = @"create table choiceness(title text primary key, content_url text, cover_image_url text)";
    // 执行SQL语句的函数
    // 参数1：数据库指针
    // 参数2：sql语句
    
    int result = sqlite3_exec(dbPoint, [sql UTF8String], NULL, NULL, NULL);
    return [self judgeResult:result text:@"创建表"];
}

// 创建横幅表
- (BOOL)createBannerGiftTable
{
   NSString *sql = @"create table BannerGift(title text , content_url text primary key,cover_image_url text)";
    int result = sqlite3_exec(dbPoint, [sql UTF8String], NULL, NULL, NULL);
    return [self judgeResult:result text:@"创建表"];
}

// 创建分类表
- (BOOL)createAllGroupTable
{
    NSString *sql = @"create table AllGroup(title text primary key, content_url text,cover_image_url text)";
    int result = sqlite3_exec(dbPoint, [sql UTF8String], NULL, NULL, NULL);
    return [self judgeResult:result text:@"创建表"];

}

// 添加精选数据
- (BOOL)insertChoiceness:(Choiceness *)choiceness
{
    NSString *sql = [NSString stringWithFormat:@"insert into choiceness values ('%@', '%@', '%@')", choiceness.title, choiceness.content_url, choiceness.cover_image_url];
    //执行sql语句,判断结果
    
    int result = sqlite3_exec(dbPoint, [sql UTF8String], NULL, NULL, NULL);
    if (result == SQLITE_OK) {
        alertView = [[UIAlertView alloc] initWithTitle:@"恭喜" message:@"收藏成功" delegate:self cancelButtonTitle:nil otherButtonTitles:nil, nil];
        [alertView show];
        [NSTimer scheduledTimerWithTimeInterval:0.8f
                                         target:self
                                       selector:@selector(dismissAlert:)
                                       userInfo:nil
                                        repeats:NO];
        [alertView release];
        
    }else{
        alertView = [[UIAlertView alloc] initWithTitle:@"您已添加收藏" message:nil delegate:self cancelButtonTitle:nil otherButtonTitles:nil, nil];
        [alertView show];
        [NSTimer scheduledTimerWithTimeInterval:0.8f
                                         target:self
                                       selector:@selector(dismissAlert:)
                                       userInfo:nil
                                        repeats:NO];
        [alertView release];
        
        
    }


    return [self judgeResult:result text:@"添加数据"];
}

// 添加横幅数据
- (BOOL)insertBannerGift:(BannerGift *)bannerGift
{
    NSString *sql = [NSString stringWithFormat:@"insert into BannerGift values ('%@', '%@', '%@')", bannerGift.title, bannerGift.content_url, bannerGift.cover_image_url];
    int result = sqlite3_exec(dbPoint, [sql UTF8String], NULL, NULL, NULL);
    if (result == SQLITE_OK) {
        alertView = [[UIAlertView alloc] initWithTitle:@"恭喜" message:@"收藏成功" delegate:self cancelButtonTitle:nil otherButtonTitles:nil, nil];
        [alertView show];
        [NSTimer scheduledTimerWithTimeInterval:0.8f
                                         target:self
                                       selector:@selector(dismissAlert:)
                                       userInfo:nil
                                        repeats:NO];
        [alertView release];
        
    }else{
        alertView = [[UIAlertView alloc] initWithTitle:@"您已添加收藏" message:nil delegate:self cancelButtonTitle:nil otherButtonTitles:nil, nil];
        [alertView show];
        [NSTimer scheduledTimerWithTimeInterval:0.8f
                                         target:self
                                       selector:@selector(dismissAlert:)
                                       userInfo:nil
                                        repeats:NO];
        [alertView release];
 
        
    }

    return [self judgeResult:result text:@"添加数据"];
}

// 添加分类数据
- (BOOL)insertAllGroup:(AllGroup *)allGroup
{
    NSString *sql = [NSString stringWithFormat:@"insert into AllGroup values ('%@', '%@', '%@')", allGroup.title, allGroup.content_url, allGroup.cover_image_url];
    int result = sqlite3_exec(dbPoint, [sql UTF8String], NULL, NULL, NULL);
    if (result == SQLITE_OK) {
        alertView = [[UIAlertView alloc] initWithTitle:@"恭喜" message:@"收藏成功" delegate:self cancelButtonTitle:nil otherButtonTitles:nil, nil];
        [alertView show];
        [NSTimer scheduledTimerWithTimeInterval:0.8f
                                         target:self
                                       selector:@selector(dismissAlert:)
                                       userInfo:nil
                                        repeats:NO];
        [alertView release];
        
    }else{
        alertView = [[UIAlertView alloc] initWithTitle:@"您已添加收藏" message:nil delegate:self cancelButtonTitle:nil otherButtonTitles:nil, nil];
        [alertView show];
        [NSTimer scheduledTimerWithTimeInterval:0.8f
                                         target:self
                                       selector:@selector(dismissAlert:)
                                       userInfo:nil
                                        repeats:NO];
        [alertView release];
        
        
    }


    return [self judgeResult:result text:@"添加数据"];
}

// 查询精选数据
- (NSMutableArray *)selectChoiceness
{
    //1.创建一个数据库的替身，存储数据库的所有操作
    sqlite3_stmt *stmt = nil;
    
    //2.准备sql语句并执行，将结果保存在stmt中
    NSString *sql = @"select * from choiceness";
    //参数1： 数据库指针，
    //参数2： sql语句
    //参数3：限制sql语句的长度（如果是-1，就是不限制）
    //参数4：替身指针的地址
    //作用 让数据库执行sql语句,并把结果保存到stmt中
    int result = sqlite3_prepare_v2(dbPoint, [ sql UTF8String] , -1, &stmt, NULL);
    
    // 3.创建一个可变数组，用于存储数据
    NSMutableArray *choicenessArr = [NSMutableArray array];
    //4. 去便利所有所有数组，创建相应的学生对象
    
    if ( result == SQLITE_OK) {
        //当sql执行成功，便利数组
        //循环遍历所有结果，每次遍历到一条数据，都会返回SQLITE_Row;如果没有数据了，就不会返回SQLITE_ROW,跳出循环
        while (sqlite3_step(stmt) == SQLITE_ROW) {
            //在循环体中去按列取数据
            
            //text类型的数据取法
            const unsigned char *titleChar =   sqlite3_column_text(stmt, 0);
            
            
            //将char *字符串转换为NSString字符串
            NSString *title = [NSString stringWithUTF8String:(const char *)titleChar];
            
            
            
            const unsigned char *content_urlChar =   sqlite3_column_text(stmt, 1);
            
            
            //将char *字符串转换为NSString字符串
            NSString *content_url = [NSString stringWithUTF8String:(const char *)content_urlChar];
            
            
            const unsigned char *cover_image_urlChar =   sqlite3_column_text(stmt, 2);
            
            
            //将char *字符串转换为NSString字符串
            NSString *cover_image_url = [NSString stringWithUTF8String:(const char *)cover_image_urlChar];
            
            Choiceness *choiceness = [[Choiceness alloc] init];
            //那获取的数据给学生对象赋值
            
            choiceness.title = title;
            choiceness.content_url = content_url;
            choiceness.cover_image_url = cover_image_url;
            [choicenessArr addObject:choiceness];
            [choiceness release];
        }
    }
    
    
    //销毁stnt替身， 把里面的操作和结果写入本地sqlite文件
    sqlite3_finalize(stmt);
    
    
    
    return choicenessArr;

 
}

// 查询横幅表
- (NSMutableArray *)selectBannerGift
{
    //1.创建一个数据库的替身，存储数据库的所有操作
    sqlite3_stmt *stmt = nil;
    
    //2.准备sql语句并执行，将结果保存在stmt中
    NSString *sql = @"select * from BannerGift";
    //参数1： 数据库指针，
    //参数2： sql语句
    //参数3：限制sql语句的长度（如果是-1，就是不限制）
    //参数4：替身指针的地址
    //作用 让数据库执行sql语句,并把结果保存到stmt中
    int result = sqlite3_prepare_v2(dbPoint, [ sql UTF8String] , -1, &stmt, NULL);
    
    // 3.创建一个可变数组，用于存储数据
    NSMutableArray *bannerGiftArr = [NSMutableArray array];
    //4. 去便利所有所有数组，创建相应的学生对象
    
    if ( result == SQLITE_OK) {
        //当sql执行成功，便利数组
        //循环遍历所有结果，每次遍历到一条数据，都会返回SQLITE_Row;如果没有数据了，就不会返回SQLITE_ROW,跳出循环
        while (sqlite3_step(stmt) == SQLITE_ROW) {
            //在循环体中去按列取数据
            
            //text类型的数据取法
            const unsigned char *titleChar =   sqlite3_column_text(stmt, 0);
            
            
            //将char *字符串转换为NSString字符串
            NSString *title = [NSString stringWithUTF8String:(const char *)titleChar];
            
            
            
            const unsigned char *content_urlChar =   sqlite3_column_text(stmt, 1);
            
            
            //将char *字符串转换为NSString字符串
            NSString *content_url = [NSString stringWithUTF8String:(const char *)content_urlChar];
            
            
            const unsigned char *cover_image_urlChar =   sqlite3_column_text(stmt, 2);
            
            
            //将char *字符串转换为NSString字符串
            NSString *cover_image_url = [NSString stringWithUTF8String:(const char *)cover_image_urlChar];
            
            BannerGift *bannerGift = [[BannerGift alloc] init];
            //那获取的数据给学生对象赋值
            
            bannerGift.title = title;
            bannerGift.content_url = content_url;
            bannerGift.cover_image_url = cover_image_url;
            [bannerGiftArr addObject:bannerGift];
            [bannerGift release];
        }
    }
    
    
    //销毁stnt替身， 把里面的操作和结果写入本地sqlite文件
    sqlite3_finalize(stmt);
    
    
    
    return bannerGiftArr;
 
}

// 查询分类数据
- (NSMutableArray *)selectAllGroup
{
    //1.创建一个数据库的替身，存储数据库的所有操作
    sqlite3_stmt *stmt = nil;
    
    //2.准备sql语句并执行，将结果保存在stmt中
    NSString *sql = @"select * from AllGroup";
    //参数1： 数据库指针，
    //参数2： sql语句
    //参数3：限制sql语句的长度（如果是-1，就是不限制）
    //参数4：替身指针的地址
    //作用 让数据库执行sql语句,并把结果保存到stmt中
    int result = sqlite3_prepare_v2(dbPoint, [ sql UTF8String] , -1, &stmt, NULL);
    
    // 3.创建一个可变数组，用于存储数据
    NSMutableArray *AllGroupArr = [NSMutableArray array];
    //4. 去便利所有所有数组，创建相应的学生对象
    
    if ( result == SQLITE_OK) {
        //当sql执行成功，便利数组
        //循环遍历所有结果，每次遍历到一条数据，都会返回SQLITE_Row;如果没有数据了，就不会返回SQLITE_ROW,跳出循环
        while (sqlite3_step(stmt) == SQLITE_ROW) {
            //在循环体中去按列取数据
            
            //text类型的数据取法
            const unsigned char *titleChar =   sqlite3_column_text(stmt, 0);
            
            
            //将char *字符串转换为NSString字符串
            NSString *title = [NSString stringWithUTF8String:(const char *)titleChar];
            
            
            
            const unsigned char *content_urlChar =   sqlite3_column_text(stmt, 1);
            
            
            //将char *字符串转换为NSString字符串
            NSString *content_url = [NSString stringWithUTF8String:(const char *)content_urlChar];
            
            
            const unsigned char *cover_image_urlChar =   sqlite3_column_text(stmt, 2);
            
            
            //将char *字符串转换为NSString字符串
            NSString *cover_image_url = [NSString stringWithUTF8String:(const char *)cover_image_urlChar];
            
            AllGroup *allGroup = [[AllGroup alloc] init];
            //那获取的数据给学生对象赋值
            
            allGroup.title = title;
            allGroup.content_url = content_url;
            allGroup.cover_image_url = cover_image_url;
            [AllGroupArr addObject:allGroup];
            [allGroup release];
        }
    }
    
    
    //销毁stnt替身， 把里面的操作和结果写入本地sqlite文件
    sqlite3_finalize(stmt);
    
    
    
    return AllGroupArr;

}
//定时器
- (void)dismissAlert:(NSTimer *)timer{
    
    
    [alertView dismissWithClickedButtonIndex:0 animated:YES];
    
}
// 删除精选数据
- (BOOL)deleteChoicenessWithTitle:(NSString * )title
{
    NSString *sql = [NSString stringWithFormat:@"delete from choiceness where title = '%@'", title];
    int result = sqlite3_exec(dbPoint, [sql UTF8String], NULL, NULL, NULL);
    return [self judgeResult:result text:@"删除数据"];

    
}

// 删除横幅数据
- (BOOL)deleteBannerGiftWithTitle:(NSString *)content_url
{
    NSString *sql = [NSString stringWithFormat:@"delete from BannerGift where content_url = '%@'", content_url];
    int result = sqlite3_exec(dbPoint, [sql UTF8String], NULL, NULL, NULL);
    return [self judgeResult:result text:@"删除数据"];
}

// 删除分类数据
- (BOOL)deleteAllGroupWithTitle:(NSString *)title
{
    NSString *sql = [NSString stringWithFormat:@"delete from AllGroup where title = '%@'", title];
    int result = sqlite3_exec(dbPoint, [sql UTF8String], NULL, NULL, NULL);
    return [self judgeResult:result text:@"删除数据"];
}




@end
